﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using Neotic.DataAccess.Sql.Operations;
using System.Linq.Expressions;

namespace Neotic.DataAccess.Sql
{
    public class SelectQuery : ISelectable, ISelectQuery, IAgregatedQuery, ISelectQueryPart
    {
        public enum TransactionMode
        {
            Default,
            Uncommitted
        }

        protected class JoinInfo
        {
            public const string INNER_JOIN = " INNER "; 
            public const string LEFT_JOIN = " LEFT "; 
            public const string RIGHT_JOIN = " RIGHT "; 

            public JoinInfo (string type,  IRowCollection table, ISqlCondition cdt)
            {
                JoinType = type;
                JoinTable = table;
                JoinCdt = cdt;
                FirstOnly = false;
            }

            public JoinInfo(string type, bool first, IRowCollection table, ISqlCondition cdt)
            {
                JoinType = type;
                JoinTable = table;
                JoinCdt = cdt;
                FirstOnly = first;
            }

            public string JoinType = INNER_JOIN;
            public IRowCollection JoinTable;
            public ISqlCondition JoinCdt;
            public bool FirstOnly;
        }

        private List<ISelectColumn> _selectColumns = new List<ISelectColumn>();
        private bool _selectDistinct = false;
        private long _startLimit = -1;
        private long _topLimit = 0;
        private Table _intoTable = null;
        private IRowCollection _fromTable = null;
        private List<JoinInfo> _joins = new List<JoinInfo>();
        private ISqlCondition _whereClause = null;
        private List<OrderClauseColumn> _orderby = null;
        private List<ISelectColumn> _groupby = null;
        private ISqlCondition _havingCdt = null;
        private SelectQuery _parentUnionQuery = null;
        private SelectQuery _parentIntersectQuery = null;
        private TransactionMode _transactionMode;

        public const string ROWNUM_COLUMN_NAME = "__PRIVATE_ROWNUM";
        public const string TOTALROWS_COLUMN_NAME = "__PRIVATE_TOTALROWS";

        public SelectQuery(TransactionMode mode = TransactionMode.Default)
        {
            _transactionMode = mode;
        }

        /// <summary>
        /// Cree une requete composée de toutes les colonnes en paramètre (select col1, col2, ... from ...)
        /// </summary>
        /// <param name="cols">liste des colonnes retourne par la requete</param>
        /// <returns>l'object SelectQuery courant</returns>
        public ISelectable Select(params ISelectColumn[] cols)
        {
            if (_selectColumns == null)
                throw new InvalidOperationException("Appel de la methode Select invalide à la suite de la methode SelectAll");

            if (_selectDistinct && _selectColumns.Count > 0)
                throw new InvalidOperationException("Appel de la methode Select invalide à la suite de la methode SelectDistinct");

            _selectDistinct = false;
            _selectColumns.AddRange(cols);

            return this;
        }

        /// <summary>
        /// Cree une requete composée de toutes les colonnes en paramètre (select discint col1, col2, ... from ...)
        /// </summary>
        /// <param name="cols">liste des colonnes retourne par la requete</param>
        /// <returns>l'object SelectQuery courant</returns>
        public ISelectable SelectDistinct(params ISelectColumn[] cols)
        {
            if (_selectColumns == null)
                throw new InvalidOperationException("Appel de la methode SelectDistinct invalide à la suite de la methode SelectAll");

            if (!_selectDistinct && _selectColumns.Count > 0)
                throw new InvalidOperationException("Appel de la methode SelectDistinct invalide à la suite de la methode Select");

            _selectDistinct = true;
            _selectColumns.AddRange(cols);

            return this;
        }

        /// <summary>
        /// Cree une requete composée de toutes les colonnes de toutes les tables (select * from ...)
        /// </summary>
        /// <returns>l'object SelectQuery courant</returns>
        public ISelectable SelectAll()
        {
            if (_selectColumns.Count > 0)
                throw new InvalidOperationException("Appel de la methode SelectAll invalide à la suite de la methode Select ou SelectDistinct");

            _selectDistinct = false;
            _selectColumns = null;

            return this;
        }

        /// <summary>
        /// Crée une requete retournant au maximum n tuples
        /// </summary>
        /// <param name="limit">nombre de ligne max remontée</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectable ISelectable.Top(long limit)
        {
            _topLimit = limit;

            return this;
        }

        /// <summary>
        /// Crée une requete retournant au 'limit' tuples a partir du 'start'ieme enregistrement 
        /// </summary>
        /// <param name="start">rang du premier item à retourner</param>
        /// <param name="limit">nombre de ligne max remontée</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectable ISelectable.Limit(int start, int limit)
        {
            _startLimit = start;
            _topLimit = limit;

            return this;
        }


        /// <summary>
        /// Crée une requete pour laquelle les informations seront stockées dans la table en paramètre
        /// </summary>
        /// <param name="t">Table recevant le resultat de la requete</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectable ISelectable.Into(Table t)
        {
            _intoTable = t;
            return this;
        }

        /// <summary>
        /// Methode d'indantificationd de la table/vue/fonction principale de la requete
        /// </summary>
        /// <param name="t">table/vue/fonction</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectQuery ISelectable.From(IRowCollection t)
        {
            if (t == null)
                throw new ArgumentNullException("parametre t null lors de l'appel de la methode From");

            _fromTable = t;

            return this;
        }

        /// <summary>
        /// Complete la requete d'un clause de jointure stricte
        /// </summary>
        /// <param name="joinTable">Table de jointure</param>
        /// <param name="joinCdt">Condition de jointure</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectQuery ISelectQuery.InnerJoin(IRowCollection joinTable, ISqlCondition joinCdt)
        {
            bool toAdd = true;
            if (this._fromTable.IsSameTable(joinTable))
                return this;
            foreach (JoinInfo j in _joins)
            {
                if (j.JoinTable.IsSameTable(joinTable))
                {
                    toAdd = false;
                    break;
                }
            }
            if (toAdd)
                _joins.Add(new JoinInfo(JoinInfo.INNER_JOIN, joinTable, joinCdt));
            return this;
        }

        /// <summary>
        /// Complete la requete d'un clause de jointure stricte avec le 1er element rencontré
        /// </summary>
        /// <param name="joinTable">Table de jointure</param>
        /// <param name="joinCdt">Condition de jointure</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectQuery ISelectQuery.InnerJoinFirst(IRowCollection joinTable, ISqlCondition joinCdt)
        {
            bool toAdd = true;
            if (this._fromTable.IsSameTable(joinTable))
                return this;
            foreach (JoinInfo j in _joins)
            {
                if (j.JoinTable.IsSameTable(joinTable))
                {
                    toAdd = false;
                    break;
                }
            }
            if (toAdd)
                _joins.Add(new JoinInfo(JoinInfo.INNER_JOIN, true, joinTable, joinCdt));
            return this;
        }


        /// <summary>
        /// Complete la requete d'un clause de jointure gauche
        /// </summary>
        /// <param name="joinTable">Table de jointure</param>
        /// <param name="joinCdt">Condition de jointure</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectQuery ISelectQuery.LeftJoin(IRowCollection joinTable, ISqlCondition joinCdt)
        {
            bool toAdd = true;
            foreach (JoinInfo j in _joins)
            {
                if (j.JoinTable.IsSameTable(joinTable))
                {
                    toAdd = false;
                    break;
                }
            }
            if (toAdd)
            _joins.Add(new JoinInfo(JoinInfo.LEFT_JOIN, joinTable, joinCdt));
            return this;
        }

        /// <summary>
        /// Complete la requete d'un clause de jointure gauche
        /// </summary>
        /// <param name="joinTable">Table de jointure</param>
        /// <param name="joinCdt">Condition de jointure</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectQuery ISelectQuery.LeftJoinFirst(IRowCollection joinTable, ISqlCondition joinCdt)
        {
            bool toAdd = true;
            foreach (JoinInfo j in _joins)
            {
                if (j.JoinTable.IsSameTable(joinTable))
                {
                    toAdd = false;
                    break;
                }
            }
            if (toAdd)
                _joins.Add(new JoinInfo(JoinInfo.LEFT_JOIN, true, joinTable, joinCdt));
            return this;
        }

        /// <summary>
        /// Complete la requete d'un clause de jointure droite
        /// </summary>
        /// <param name="joinTable">Table de jointure</param>
        /// <param name="joinCdt">Condition de jointure</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectQuery ISelectQuery.RightJoin(IRowCollection joinTable, ISqlCondition joinCdt)
        {
            bool toAdd = true;
            foreach (JoinInfo j in _joins)
            {
                if (j.JoinTable.IsSameTable(joinTable))
                {
                    toAdd = false;
                    break;
                }
            }
            if (toAdd)
            _joins.Add(new JoinInfo(JoinInfo.RIGHT_JOIN, joinTable, joinCdt));
            return this;
        }

        /// <summary>
        /// Complete la requete d'un clause de filtre
        /// Si la methode est appelée plusieurs fois sur la même instance,
        /// les conditions en paramètre seront associées avec l'operateur AND
        /// </summary>
        /// <param name="whereClause">condition de filtre</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectQuery ISelectQuery.Where(ISqlCondition whereClause)
        {
            if (_whereClause == null)
                _whereClause = whereClause;
            else
                _whereClause = new And(_whereClause, whereClause);

            return this;
        }

        //public static IEnumerable<TSource> Where<TSource>(Expression<Func<TSource, bool>> predicate)
        //{
        //    // hacks all the way
        //    dynamic operation = predicate.Body;
        //    dynamic left = operation.Left;
        //    dynamic right = operation.Right;

        //    var ops = new Dictionary<ExpressionType, String>();
        //    ops.Add(ExpressionType.Equal, "=");
        //    ops.Add(ExpressionType.GreaterThan, ">");
        //    // add all required operations here            

        //    // Instead of SELECT *, select all required fields, since you know the type
        //    var q = String.Format("SELECT * FROM {0} WHERE {1} {2} {3}", typeof(TSource), left.Member.Name, ops[operation.NodeType], right.Value);
        //    return source.RunQuery(q);
        //}
        /// <summary>
        /// Ne fonctionne pas encore !!!!
        /// </summary>
        /// <param name="predicate"></param>
        /// <returns></returns>
        ISelectQuery ISelectQuery.Where(Expression<Func<ISqlCondition, object>> predicate)
        {
            ISqlCondition cdt;

            try
            {
                cdt = OperatorExpressionManager.FindOp(predicate);
            }
            catch(Exception ex)
            {
                throw new Exception("Erreur lors de l'analyse de l'expression de la clause Where", ex);
            }

            if (cdt == null)
                throw new NotImplementedException("L'operateur ISqlCondition correspond à ce prédicate n'est pas implementé");

            return ((ISelectQuery)this).Where(cdt);
        }

        /// <summary>
        /// Complete la requete d'une clause Order by permettant de trier le resultat de la requete
        /// Si la methode est appelée plusieurs fois sur la même instance,
        /// les colonnes seront ajoutées aux colonnes déjà définies pour le tri.
        /// </summary>
        /// <param name="cols">Liste des colonnes utilisées pour le tri avec le sens du tri</param>
        /// <returns>l'object SelectQuery courant</returns>
        IExecutableQuery<SelectQuery> IOrderedQuery.OrderBy(params OrderClauseColumn[] cols)
        {
            if (cols == null)
                throw new ArgumentNullException("paramètre cols attendu");

            if (_orderby == null)
                _orderby = new List<OrderClauseColumn>(cols);
            else
                _orderby.AddRange(cols);

            return this;
        }

        /// <summary>
        /// Complete la requete d'une clause group by 
        /// Si la methode est appelée plusieurs fois sur la même instance,
        /// les colonnes seront ajoutées aux colonnes déjà définies 
        /// </summary>
        /// <param name="cols">Liste des colonnes utilisées pour le regroupement</param>
        /// <returns>l'object SelectQuery courant</returns>
        IAgregatedQuery ISelectQuery.GroupBy(params ISelectColumn[] cols)
        {
            if (cols == null)
                throw new ArgumentNullException("paramètre cols attendu");

            if (_groupby == null)
                _groupby = new List<ISelectColumn>(cols);
            else
                _groupby.AddRange(cols);

            return this; 
        }

        /// <summary>
        /// Complete la requete d'une clause Having
        /// Si la methode est appelée plusieurs fois sur la même instance,
        /// les conditions en paramètre seront associées avec l'operateur AND
        /// </summary>
        /// <param name="havingCdt">Condition sur le regroupement</param>
        /// <returns>l'object SelectQuery courant</returns>
        ISelectQueryPart IAgregatedQuery.Having(ISqlCondition havingCdt)
        {
            if (_havingCdt == null)
                _havingCdt = havingCdt;
            else
                _havingCdt = new And(_havingCdt, havingCdt);

            return this;
        }

        /// <summary>
        /// Construit une nouvelle requete et associe la requete courant comme sous requete
        /// </summary>
        /// <returns>nouvelle instance d'une requete Select correspondant à la sous-requete</returns>
        ISelectable ISelectQueryPart.UnionAll()
        {
            SelectQuery s = new SelectQuery();
            s._parentUnionQuery = this;
            return s;
        }

        /// <summary>
        /// Construit une nouvelle requete et associe la requete courant comme sous requete
        /// </summary>
        /// <returns>nouvelle instance d'une requete Select correspondant à la sous-requete</returns>
        ISelectable ISelectQueryPart.Intersect()
        {
            SelectQuery s = new SelectQuery();
            s._parentIntersectQuery = this;
            return s;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        string IExecutableQuery<SelectQuery>.ToSql(DbParameterCollection cmdParams)
        {
            this.Validate(true);

            StringBuilder sql = new StringBuilder();

            if (_parentUnionQuery != null)
                sql.AppendLine(((IExecutableQuery<SelectQuery>)_parentUnionQuery).ToSql(cmdParams)).AppendLine("UNION ALL ");

            if (_parentIntersectQuery != null)
                sql.AppendLine(((IExecutableQuery<SelectQuery>)_parentIntersectQuery).ToSql(cmdParams)).AppendLine("INTERSECT ");

            sql.Append("SELECT ");

            if (_topLimit > 0 && _startLimit < 0)
                sql.Append(" Top ").Append(_topLimit);

            if (_selectDistinct)
                sql.Append(" DISTINCT ");

            if (_selectColumns == null || _selectColumns.Count == 0)
                sql.Append(" * ");
            else
                this.AppendColumns(sql, _selectColumns, true);

            if (_intoTable != null)
                sql.Append(" INTO ").Append(_intoTable.FullName);

            sql.Append(" FROM ").AppendLine(_fromTable.FullAliasName);

            foreach (JoinInfo i in _joins)
                AppendJoinClause(sql, i, cmdParams);

            if (_whereClause != null)
                sql.Append(" WHERE ").AppendLine(_whereClause.ToSql(cmdParams));


            if (_groupby != null)
            {
                sql.Append(" GROUP BY ");
                AppendColumns(sql, _groupby, false);

                if (_havingCdt != null)
                {
                    sql.Append(" HAVING ");
                    sql.AppendLine(_havingCdt.ToSql(cmdParams));
                }
            }

            if (_startLimit >= 0)
            {
                StringBuilder sql2 = new StringBuilder("with __datacte as (");
                sql2.Append(sql.ToString()).Append(") select * from ( SELECT ROW_NUMBER() OVER(ORDER BY ");
                
                
                if (_orderby != null)
                {
                    string sep = " ";
                    foreach (OrderClauseColumn c in _orderby)
                    {
                        sql2.Append(sep).Append(c.ToSql());
                        sep = ",";
                    }
                }
                else
                {
                    sql2.Append(_selectColumns[0].AliasName);
                }

                sql2.Append(") AS ").Append(ROWNUM_COLUMN_NAME).Append(", count(*) over() ").Append(TOTALROWS_COLUMN_NAME).Append(", * from __datacte ) as __pagedData ");
                sql2.Append("where ").Append(ROWNUM_COLUMN_NAME).Append(" between ").Append(_startLimit + 1).Append(" and ").Append(_startLimit + _topLimit);
                sql = sql2;
            }
            else
            if (_orderby != null)
            {
                sql.Append(" ORDER BY");
                string sep = " ";
                foreach(OrderClauseColumn c in _orderby)
                {
                    sql.Append(sep).Append(c.ToSql());
                    sep = ",";
                }
            }

            string mySql = sql.ToString();

            switch (_transactionMode)
            {
                case TransactionMode.Uncommitted:
                    mySql = string.Concat("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; ", mySql);
                    break;
            }

            return mySql;
        }

        private void AppendColumns(StringBuilder sql, List<ISelectColumn> cols, bool asStatment)
        {
            string sep = " ";
            foreach (ISelectColumn c in cols)
            {
                sql.Append(sep).Append(asStatment? c.FullAliasName : c.AliasName);
                sep = ",";
            }
        }

        private void AppendJoinClause(StringBuilder sql, JoinInfo i, DbParameterCollection cmdParams)
        {
            if (i.FirstOnly)
            {
                if (i.JoinType == JoinInfo.INNER_JOIN)
                    sql.Append(" CROSS APPLY (");
                else
                    sql.Append(" OUTER APPLY (");

                sql.Append("SELECT  TOP 1 * FROM ").Append(i.JoinTable.FullName).Append(" WHERE ").Append(i.JoinCdt.ToSql(cmdParams));
                sql.Append(") ").Append(i.JoinTable.AliasName);
            }
            else if (i.JoinTable is SubQuery)
                sql.Append(i.JoinType).Append(" JOIN ").Append(((SubQuery)i.JoinTable).ToSql(cmdParams)).Append(" ON (").Append(i.JoinCdt.ToSql(cmdParams)).AppendLine(") ");
            else
                sql.Append(i.JoinType).Append(" JOIN ").Append(i.JoinTable.FullAliasName).Append(" ON (").Append(i.JoinCdt.ToSql(cmdParams)).AppendLine(") ");
        }

        SelectQuery IExecutableQuery<SelectQuery>.Query
        {
            get { return this; }
        }

        public static DataTable Execute(DbProviderFactory factory, DbConnection connection, IExecutableQuery<SelectQuery> query)
        {
            if (factory == null)
                throw new ArgumentNullException("paramètre factory obligatoire pour executer la requete");

            if (connection == null)
                throw new ArgumentNullException("paramètre connection obligatoire pour executer la requete");

            if (query == null)
                throw new ArgumentNullException("paramètre query obligatoire");

            query.Query.Validate(true);

            using (DbCommand command = factory.CreateCommand())
            {
            
                command.CommandType = CommandType.Text;
                command.CommandText = query.ToSql(command.Parameters);
                command.Connection = connection;


                if (query.Query._intoTable != null)
                {
                    command.ExecuteNonQuery();
                    return null;
                }
                else
                {
                    using (DbDataAdapter adapter = factory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = command;
                        DataTable table = new DataTable();
                        adapter.Fill(table);
                        return table;
                    }
                }
            }
        }

        /// <summary>
        /// Valide la composition d'une requete
        /// </summary>
        /// <param name="throwEx">Indique si une exception est levée si une anomalie est trouvée</param>
        /// <returns>Liste des anomalies trouvées</returns>
        /// <exception cref="InvalidOperationException">leve une InvalidOperationException avec le message en paramètre si throwEx est vrai</exception>
        public string[] Validate(bool throwEx)
        {
            List<string> messages = new List<string>();
 
            if (_fromTable == null)
            {
                string msg = "Methode From non appelée : Table principale inconnue";
                messages.Add(msg);
                if (throwEx)
                    throw new InvalidOperationException(msg);
            }

            return messages.ToArray();
        }
    }
}
